﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;

namespace GG.Data
{
    public class MyData
    {

        private string _conStringLog = ConfigurationManager.ConnectionStrings["GG_EmailLog_DBConnectionString"].ToString();
        private string _conString = ConfigurationManager.ConnectionStrings["GG_DBConnectionString"].ToString();

        public MyData()
        {
        }

        //public DataSet GetAgentCallsList()
        //{
        //    SqlCommand _sqlCmd = null;
        //    SqlConnection _sqlConn = new SqlConnection();
        //    DataSet _dsAgentCallsList = new DataSet();

        //    try
        //    {
        //        _sqlConn.ConnectionString = _conString;

        //        Int16 _CurrentTime = 7;
        //        DateTime _dtTime = GG.Global.General.GetUTCDateTime();
        //        //_dtTime = _dtTime.AddHours(-8);
        //        string _strCurrentTime = _dtTime.Hour.ToString("00");
        //        if (_strCurrentTime != string.Empty)
        //        {
        //            if (!Int16.TryParse(_strCurrentTime, out _CurrentTime))
        //            {
        //                _CurrentTime = 7;
        //            }
        //        }

        //        //_CurrentTime = 7; // Test
        //        DateTime d = GG.Global.General.GetUTCDateTime();
        //        DateTime _dtDate = GG.Global.General.GetUTCDateTime();
        //        if (d.DayOfWeek == DayOfWeek.Monday)
        //        {
        //            _dtDate = d.AddDays(-3);
        //        }
        //        else
        //        {
        //            _dtDate = d.AddDays(-1);
        //        }

        //        string _strDate = _dtDate.Year.ToString("0000") + _dtDate.Month.ToString("00") + _dtDate.Day.ToString("00");
        //        _sqlConn.Open();
        //        _sqlCmd = new SqlCommand();
        //        _sqlCmd.Connection = _sqlConn;
        //        _sqlCmd.CommandType = CommandType.StoredProcedure;
        //        _sqlCmd.CommandText = "sprocCXTHGetAgentCallsListSummarySelectedList";
        //        SqlParameter _sqlPara1 = new SqlParameter();
        //        _sqlPara1.ParameterName = "@Date";
        //        _sqlCmd.Parameters.Add(_sqlPara1);
        //        _sqlCmd.Parameters[0].Value = _strDate;

        //        SqlParameter _sqlPara2 = new SqlParameter();
        //        _sqlPara2.ParameterName = "@CurrentTime";
        //        _sqlCmd.Parameters.Add(_sqlPara2);
        //        _sqlCmd.Parameters[1].Value = _CurrentTime;

        //        SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);
        //        _sqlDa.Fill(_dsAgentCallsList);

        //    }
        //    catch (Exception Ex)
        //    {
        //        Ex.ToString();

        //    }
        //    finally
        //    {
        //        if (_sqlCmd != null)
        //        {
        //            _sqlCmd = null;
        //        }

        //        if (_sqlConn.State != ConnectionState.Closed)
        //        {
        //            _sqlConn.Close();
        //            _sqlConn = null;
        //        }

        //    }
        //    return _dsAgentCallsList;
        //}

        public Boolean AssignLeads(string FromDate, string ToDate)
        {
            bool _result = true;
            //SqlCommand _sqlCmd = null;
            //SqlConnection _sqlConn = new SqlConnection();
            //DataSet _dsOffLineQuotes = new DataSet();
            //try
            //{
            //    _sqlConn.ConnectionString = _conString;
            //    _sqlConn.Open();

            //    _sqlCmd = new SqlCommand();
            //    _sqlCmd.Connection = _sqlConn;
            //    _sqlCmd.CommandType = CommandType.StoredProcedure;
            //    _sqlCmd.CommandText = "sprocCXCRMAutoLeadsAssigntoAgentSelectedList";

            //    SqlParameter _sqlPara1 = new SqlParameter();
            //    _sqlPara1.ParameterName = "@FromDate";
            //    _sqlCmd.Parameters.Add(_sqlPara1);
            //    _sqlCmd.Parameters[0].Value = FromDate;

            //    SqlParameter _sqlPara2 = new SqlParameter();
            //    _sqlPara2.ParameterName = "@ToDate";
            //    _sqlCmd.Parameters.Add(_sqlPara2);
            //    _sqlCmd.Parameters[1].Value = ToDate;

            //    _sqlCmd.ExecuteNonQuery();
            //}
            //catch (Exception Ex)
            //{
            //    _result = false;
            //}
            //finally
            //{
            //    if (_sqlCmd != null)
            //    {
            //        _sqlCmd = null;
            //    }

            //    if (_sqlConn.State != ConnectionState.Closed)
            //    {
            //        _sqlConn.Close();
            //        _sqlConn = null;
            //    }
            //}
            return _result;
        }

        public DataSet GetAgentLeads()
        {
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();

            DataSet _dsAgentLeads = new DataSet();

            try
            {
                _sqlConn.ConnectionString = _conString;

                DateTime d = GG.Global.General.GetUTCDateTime();
                DateTime _dtFromDate = GG.Global.General.GetUTCDateTime();
                DateTime _dtToDate = GG.Global.General.GetUTCDateTime();
                DateTime _dtDate = GG.Global.General.GetUTCDateTime();
                if (d.DayOfWeek == DayOfWeek.Monday)
                {
                    _dtFromDate = d.AddDays(-3);
                }
                else
                {
                    _dtFromDate = d.AddDays(-1);
                }
                _dtToDate = d.AddDays(-1);
                if (_dtDate.ToString("tt").ToUpper() == "AM")
                {
                    _dtFromDate = GetExceptEventDays(_dtFromDate);
                }
                else
                {
                    _dtFromDate = _dtDate;
                    _dtToDate = _dtDate;
                }

                string _strFromDate = _dtFromDate.Year.ToString("0000") + _dtFromDate.Month.ToString("00") + _dtFromDate.Day.ToString("00");
                string _strToDate = _dtToDate.Year.ToString("0000") + _dtToDate.Month.ToString("00") + _dtToDate.Day.ToString("00");

                _sqlConn.Open();
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.StoredProcedure;
                _sqlCmd.CommandText = "sprocCXTHGetAgentLeadsSummarySelectedList";
                SqlParameter _sqlPara1 = new SqlParameter();
                _sqlPara1.ParameterName = "@FromDate";
                _sqlCmd.Parameters.Add(_sqlPara1);
                _sqlCmd.Parameters[0].Value = _strFromDate;

                SqlParameter _sqlPara2 = new SqlParameter();
                _sqlPara2.ParameterName = "@ToDate";
                _sqlCmd.Parameters.Add(_sqlPara2);
                _sqlCmd.Parameters[1].Value = _strToDate;

                SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);
                _sqlDa.Fill(_dsAgentLeads);

            }
            catch (Exception Ex)
            {
                Ex.ToString();

            }
            finally
            {
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }
            }
            return _dsAgentLeads;
        }

        public void InsertMailCountLog(string applicationName)
        {
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();
            DataSet _dsOffLineQuotes = new DataSet();
            try
            {
                string islive = ConfigurationManager.AppSettings["islive"].ToString();
                _sqlConn.ConnectionString = _conStringLog;
                _sqlConn.Open();

                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.StoredProcedure;
                _sqlCmd.CommandText = "sproctblGGEmailsLogInsertSingleItem";

                SqlParameter _sqlPara1 = new SqlParameter();
                _sqlPara1.ParameterName = "@IsLive";
                _sqlCmd.Parameters.Add(_sqlPara1);
                _sqlCmd.Parameters[0].Value = islive;

                SqlParameter _sqlPara2 = new SqlParameter();
                _sqlPara2.ParameterName = "@ApplicationName";
                _sqlCmd.Parameters.Add(_sqlPara2);
                _sqlCmd.Parameters[1].Value = applicationName;

                SqlParameter _sqlPara3 = new SqlParameter();
                _sqlPara3.ParameterName = "@EmailCount";
                _sqlCmd.Parameters.Add(_sqlPara3);
                _sqlCmd.Parameters[2].Value = 1;

                _sqlCmd.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }
            }
        }

        public DateTime GetExceptEventDays(DateTime _dtDate)
        {
            DateTime _dtSlectDate = _dtDate;
            string _strFromDate = _dtSlectDate.Year.ToString("0000") + _dtSlectDate.Month.ToString("00") + _dtSlectDate.Day.ToString("00");
            int NoofDay = 0;
            try
            {
                NoofDay = GetEventDay(_strFromDate);
                while (NoofDay > 0)
                {
                    _dtSlectDate = _dtSlectDate.AddDays(-NoofDay);
                    if (_dtSlectDate.DayOfWeek == DayOfWeek.Sunday)
                    {
                        _dtSlectDate = _dtSlectDate.AddDays(-2);
                    }
                    _strFromDate = _dtSlectDate.Year.ToString("0000") + _dtSlectDate.Month.ToString("00") + _dtSlectDate.Day.ToString("00");
                    NoofDay = GetEventDay(_strFromDate);
                }
                return _dtSlectDate;
            }
            catch (Exception Ex)
            {
                return _dtDate;
            }
        }

        public Int32 GetEventDay(string FromDate)
        {
            Int32 RecordCount = 0;
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();
            DataSet _dsEventCount = new DataSet();
            try
            {
                string strQuery = @"SELECT COUNT(*) AS RecordCount FROM tblCXCRMEvents ";
                strQuery += " WHERE CONVERT(VARCHAR(8), EventDate, 112) = '" + FromDate + "'";
                _sqlConn.ConnectionString = _conString;
                _sqlConn.Open();
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.Text;
                _sqlCmd.CommandText = strQuery;

                SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);
                _sqlDa.Fill(_dsEventCount);
                if (_dsEventCount != null && _dsEventCount.Tables.Count > 0 && _dsEventCount.Tables[0].Rows.Count > 0)
                {
                    if (!Int32.TryParse(_dsEventCount.Tables[0].Rows[0]["RecordCount"].ToString(), out RecordCount))
                    {
                        RecordCount = 0;
                    }
                }
            }
            catch (Exception Ex)
            {
                Ex.ToString();

            }
            finally
            {
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }

            }

            return RecordCount;
        }

        public Int32 GetEventCount()
        {
            Int32 RecordCount = 0;
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();
            DataSet _dsEventCount = new DataSet();
            try
            {
                string strQuery = @"SELECT COUNT(*) AS RecordCount FROM tblCXCRMEvents ";
                strQuery += " WHERE CONVERT(VARCHAR(8), EventDate, 112) = CONVERT(VARCHAR(8),dbo.fn_getUTCDateTime(), 112)";
                _sqlConn.ConnectionString = _conString;
                _sqlConn.Open();
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.Text;
                _sqlCmd.CommandText = strQuery;

                SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);
                _sqlDa.Fill(_dsEventCount);
                if (_dsEventCount != null && _dsEventCount.Tables.Count > 0 && _dsEventCount.Tables[0].Rows.Count > 0)
                {
                    if (!Int32.TryParse(_dsEventCount.Tables[0].Rows[0]["RecordCount"].ToString(), out RecordCount))
                    {
                        RecordCount = 0;
                    }
                }
            }
            catch (Exception Ex)
            {
                Ex.ToString();

            }
            finally
            {
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }

            }

            return RecordCount;
        }

    }
}
